from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import time,pymysql
from selenium.webdriver import ActionChains
from selenium.webdriver.support.ui import WebDriverWait
options = Options()
options.add_argument('--disable-infobars')
options.add_argument('--incognito')
# options.add_argument('--headless')
bro = webdriver.Chrome(executable_path='C:\drf2\drf2\chromedriver.exe',chrome_options=options)
bro.maximize_window()
# bro.get('')
#定义隐式等待
bro.implicitly_wait(10)

def offline_rate():
    u = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/div[1]/div[1]/input')
    u.send_keys('')
    # sleep(1)
    p = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/div[1]/div[2]/input')
    p.send_keys('')
    # sleep(1)

    # 点击登录按钮
    bro.find_element_by_class_name('el-button').click()

    # 选择项目
    bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/form/div[1]/div/div/div/input').click()
    btn = bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]/span')
    # 移动元素element对象的顶端与当前窗口的顶部对齐
    # https://blog.csdn.net/lilongsy/article/details/76142497 Selenium下拉滚动条
    bro.execute_script("arguments[0].scrollIntoView(true);", btn)
    btn.click()
    # bd = bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]/span')
    # actions = ActionChains(bro)
    # actions.move_to_element(bd).click().perform()

    bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]')
    # 点击确定按钮
    time.sleep(1)
    bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/button/span').click()
    # 点击统计分析
    bro.find_element_by_xpath('//*[@id="app"]/div/div[1]/div[2]/div[1]/div/ul/div/li[3]/div/span').click()
    # 点击市州设备情况
    bro.find_element_by_xpath('//*[@id="app"]/div/div[1]/div[2]/div[1]/div/ul/div/li[3]/ul/li[3]/span').click()
    # cookies = bro.get_cookies()
    # sessionid = bro.execute_script('return localStorage.getItem("__TOKEN__");')
    time.sleep(3)
    # tr[position()> last()-3]从倒数第3个开始 选择所有的;也可以position()>=1
    trs = bro.find_elements_by_xpath('//tbody/tr[position()> 0]')
    tr_list = []
    for tr in trs:
        tr = tr.text.split('\n')
        if tr[2] == '0' or tr[1] in('张家界市普适型地灾监测预警项目（第一批）','长沙省厅专业及普适型地灾监测项目（2+20个点）','湘西州新增20个点普适化监测项目'):
            continue
        if '张家界' in tr[0]:
            tr[0] = tr[0][:3]
        else:
            tr[0] = tr[0][:2]
        tr.pop()
        tr[-1] = tr[-1].strip('%')
        tr_list.append(tr)
    print(tr_list)
    # bro.save_screenshot('a2.png')
    bro.quit()
    return tr_list

def conn_mysql(sql):
    dbparam = {
        'host': '127.0.0.1',
        # 'host': '10.10.10.180',
        'port': 3306,
        'user': 'root',
        'password': '1024',
        'database': 'alerts',
        'charset': 'utf8'
    }
    conn = pymysql.connect(**dbparam)
    cursor = conn.cursor()

    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print('入库失败', e)
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

nlist = offline_rate()
sql1 = "delete from offline1170"
sql = "insert into offline1170 (yys,xxz,css,sys,zjjs,zzs,cds,ld) values(%s,%s,%s,%s,%s,%s,%s,%s)" % (nlist[0][5],nlist[1][5],nlist[2][5],nlist[3][5],nlist[4][5],nlist[5][5],nlist[6][5],nlist[7][5])
print(sql)
# sql = 'SELECT county,red_alerts,orange_alerts,yellow_alerts,blue_alerts from spider1170'
conn_mysql(sql1)
conn_mysql(sql)
